
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[usp_ShowRecruitPositionStudent]

	(
	@param_type NVARCHAR(50)

	)

AS
	/* SET NOCOUNT ON */
	IF @param_type='school'
	BEGIN
	 SET @param_type='校内专场'
	 END
	 ELSE
	 BEGIN
	 IF @param_type='web'
	 BEGIN
	  SET @param_type='网络招聘会'
	 END
	 END
	
	SELECT  topic,
	        current_stage ,
	        degree ,
	        dbo.JOL_Recruitment_Position.id ,
	        position ,
	        professional ,
	        quantity ,
	        target,
	        employing_unit,
	        JOL_Employer_Name,
	        participants_apply
	 FROM dbo.JOL_Recruitment_Participants_apply,dbo.JOL_Recruitment_Position,dbo.JOL_Recruitment,dbo.JOL_Employer
	 WHERE
	 participants_apply=dbo.JOL_Recruitment_Participants_apply.id AND recruitment=dbo.JOL_Recruitment.id AND employing_unit=JOL_Employer_No and type=@param_type AND dbo.JOL_Recruitment.state='True' AND dbo.JOL_Recruitment_Participants_apply.state='True' AND is_visible='True' AND dbo.JOL_Recruitment_Position.state='False'
	 ORDER BY dbo.JOL_Recruitment_Position.update_time,dbo.JOL_Recruitment_Position.add_time DESC
	
	RETURN

GO
